<?php
// 数据库连接配置
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "exam_system";

// 创建数据库连接（MAMP环境配置，使用套接字）
ini_set('mysqli.default_socket', '/Applications/MAMP/tmp/mysql/mysql.sock');
$conn = new mysqli($servername, $username, $password);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 创建数据库（如果不存在）
$sql = "CREATE DATABASE IF NOT EXISTS $dbname";
if ($conn->query($sql) === FALSE) {
    echo "创建数据库错误: " . $conn->error;
}

// 选择数据库
$conn->select_db($dbname);

// 创建习题集表
$create_exercise_sets_table = "CREATE TABLE IF NOT EXISTS exercise_sets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

if ($conn->query($create_exercise_sets_table) === FALSE) {
    echo "创建习题集表错误: " . $conn->error;
}

// 创建章节表
$create_chapters_table = "CREATE TABLE IF NOT EXISTS chapters (
    id INT AUTO_INCREMENT PRIMARY KEY,
    exercise_set_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_chapter (exercise_set_id, name),
    FOREIGN KEY (exercise_set_id) REFERENCES exercise_sets(id) ON DELETE CASCADE
)";

if ($conn->query($create_chapters_table) === FALSE) {
    echo "创建章节表错误: " . $conn->error;
}

// 创建数据表（修改为使用chapter_id外键）
$create_table_sql = "CREATE TABLE IF NOT EXISTS exam_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    exam_date DATE NOT NULL,
    question_type VARCHAR(50) NOT NULL,
    question_number VARCHAR(50) NOT NULL,
    exercise_set_id INT DEFAULT NULL,
    chapter_id INT DEFAULT NULL,
    notes TEXT,
    error_reason TEXT,
    reflection TEXT,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    duration INT NOT NULL,
    correctness VARCHAR(20) DEFAULT '未批改',
    FOREIGN KEY (exercise_set_id) REFERENCES exercise_sets(id) ON DELETE SET NULL,
    FOREIGN KEY (chapter_id) REFERENCES chapters(id) ON DELETE SET NULL
)";

// 执行创建表语句
if ($conn->query($create_table_sql) === FALSE) {
    echo "创建exam_records表错误: " . $conn->error;
}

// 检查表结构并进行必要的修改
$check_columns = array();
$result = $conn->query("SHOW COLUMNS FROM exam_records");
while ($row = $result->fetch_assoc()) {
    $check_columns[] = $row['Field'];
}

// 如果存在旧的chapter字段但不存在chapter_id字段，进行迁移
if (in_array('chapter', $check_columns) && !in_array('chapter_id', $check_columns)) {
    // 添加新字段
    $conn->query("ALTER TABLE exam_records ADD COLUMN exercise_set_id INT DEFAULT NULL, ADD COLUMN chapter_id INT DEFAULT NULL");
    $conn->query("ALTER TABLE exam_records ADD FOREIGN KEY (exercise_set_id) REFERENCES exercise_sets(id) ON DELETE SET NULL");
    $conn->query("ALTER TABLE exam_records ADD FOREIGN KEY (chapter_id) REFERENCES chapters(id) ON DELETE SET NULL");
    
    // 获取或创建默认习题集
    $result = $conn->query("SELECT id FROM exercise_sets LIMIT 1");
    $default_set_id = 1;
    if ($result && $row = $result->fetch_assoc()) {
        $default_set_id = $row['id'];
    }
    
    // 为现有的章节名称创建对应的章节记录并更新外键
    $result = $conn->query("SELECT DISTINCT chapter FROM exam_records WHERE chapter IS NOT NULL AND chapter != ''");
    while ($row = $result->fetch_assoc()) {
        $chapter_name = $row['chapter'];
        // 检查是否已存在该章节
        $stmt = $conn->prepare("SELECT id FROM chapters WHERE exercise_set_id = ? AND name = ?");
        $stmt->bind_param("is", $default_set_id, $chapter_name);
        $stmt->execute();
        $chapter_result = $stmt->get_result();
        
        if ($chapter_row = $chapter_result->fetch_assoc()) {
            $chapter_id = $chapter_row['id'];
        } else {
            // 创建新章节
            $stmt_insert = $conn->prepare("INSERT INTO chapters (exercise_set_id, name) VALUES (?, ?)");
            $stmt_insert->bind_param("is", $default_set_id, $chapter_name);
            $stmt_insert->execute();
            $chapter_id = $stmt_insert->insert_id;
            $stmt_insert->close();
        }
        
        // 更新exam_records中的chapter_id和exercise_set_id
        $update_stmt = $conn->prepare("UPDATE exam_records SET chapter_id = ?, exercise_set_id = ? WHERE chapter = ?");
        $update_stmt->bind_param("iis", $chapter_id, $default_set_id, $chapter_name);
        $update_stmt->execute();
        $update_stmt->close();
        
        $stmt->close();
    }
    
    // 删除旧的chapter字段
    $conn->query("ALTER TABLE exam_records DROP COLUMN chapter");
} else if (!in_array('chapter_id', $check_columns)) {
    // 如果是新表，直接添加外键字段
    $conn->query("ALTER TABLE exam_records ADD COLUMN exercise_set_id INT DEFAULT NULL, ADD COLUMN chapter_id INT DEFAULT NULL");
    $conn->query("ALTER TABLE exam_records ADD FOREIGN KEY (exercise_set_id) REFERENCES exercise_sets(id) ON DELETE SET NULL");
    $conn->query("ALTER TABLE exam_records ADD FOREIGN KEY (chapter_id) REFERENCES chapters(id) ON DELETE SET NULL");
}

// 设置字符集为UTF-8
$conn->set_charset("utf8mb4");

// 检查是否已有习题集，如果没有则插入一些示例数据
$check_sets_result = $conn->query("SELECT COUNT(*) as count FROM exercise_sets");
if ($check_sets_result && $row = $check_sets_result->fetch_assoc()) {
    if (intval($row['count']) === 0) {
        // 插入示例习题集
        $sample_sets = array(
            array('基础习题集', '基础知识练习题'),
            array('进阶习题集', '提高难度的习题'),
            array('模拟考试题集', '模拟考试用题')
        );
        
        $stmt = $conn->prepare("INSERT INTO exercise_sets (name, description) VALUES (?, ?)");
        foreach ($sample_sets as $set) {
            $stmt->bind_param("ss", $set[0], $set[1]);
            $stmt->execute();
        }
        $stmt->close();
        
        // 为每个习题集添加示例章节
        $sets_result = $conn->query("SELECT id FROM exercise_sets");
        $set_index = 1;
        while ($set_row = $sets_result->fetch_assoc()) {
            $set_id = $set_row['id'];
            $chapters = array(
                array("第" . $set_index . "章 基础概念", "基础知识部分"),
                array("第" . $set_index . "章 进阶应用", "应用技能部分"),
                array("第" . $set_index . "章 综合练习", "综合能力部分")
            );
            
            $chapter_stmt = $conn->prepare("INSERT INTO chapters (exercise_set_id, name, description) VALUES (?, ?, ?)");
            foreach ($chapters as $chapter) {
                $chapter_stmt->bind_param("iss", $set_id, $chapter[0], $chapter[1]);
                $chapter_stmt->execute();
            }
            $chapter_stmt->close();
            $set_index++;
        }
    } else {
        // 检查是否需要为现有习题集添加章节
        $sets_result = $conn->query("SELECT id FROM exercise_sets");
        while ($set_row = $sets_result->fetch_assoc()) {
            $set_id = $set_row['id'];
            $check_chapters = $conn->query("SELECT COUNT(*) as count FROM chapters WHERE exercise_set_id = $set_id");
            if ($check_chapters && $chapter_row = $check_chapters->fetch_assoc()) {
                // 不再自动添加默认章节，让用户自己管理章节
                // 这样用户就可以删除所有章节了
                if (intval($chapter_row['count']) === 0) {
                    // 不做任何操作，允许习题集没有章节
                }
            }
        }
    }
}

// 移除自动插入示例记录的功能，避免删除所有记录后自动生成默认数据
// 现在用户可以完全清空记录，不再自动生成示例数据


// 关闭连接的函数
function close_connection($conn) {
    $conn->close();
}
?>